package com.hongye.app.model;

import java.util.List;

import com.hongye.app.controller.UserAppInfoController;
import com.hongye.util.Utils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;

/**
 * @author changpf
 */
public class GoodsModel extends Model<GoodsModel> {
	
	private static final long serialVersionUID = -6725328262425207984L;
	
	public static final GoodsModel dao=new GoodsModel();
	
	/**
	 * 得到所有审核通过的商品--商品详情
	 * web详情用
	 * @param goodsid
	 * @return
	 */
	public GoodsModel getGoods(int goodsid){
		String sql="SELECT g.id, g.thumb,  g.sale, stock,n_reason, g_name, videoname, g.real_price, g.ratio, g.c_time, g.seller_id, g.c_id, content, b.phone, gc.c_name, nick, avatar, sales FROM goods g ,buyer b, goods_categroy gc WHERE g.seller_id=b.user_id and g.sale=0 and g.c_id = gc.id and g.id = "+goodsid+"";
		GoodsModel goods = GoodsModel.dao.findFirst(sql);
		return goods;
	}
	
	/**
	 * 买家得到商品信息
	 * @author liushaomin
	 * 2017年7月28日
	 * @param goodsid
	 * @return
	 */
	public GoodsModel getGoods_Seller(int goodsid){
		String sql="SELECT g.id, g.thumb, g.audit, g.sale, g.stock, g.n_reason, g.g_name, g.videoname, g.real_price, g.c_time, g.c_id, g.content, gc.c_name FROM goods g ,goods_categroy gc WHERE g.c_id = gc.id and g.id = "+goodsid+"";
		GoodsModel goods = GoodsModel.dao.findFirst(sql);
		return goods;
	}
	
	/**
	 * 确认订单 
	 * 得到商品list 根据ids 加入商品数量
	 * @author liushaomin
	 * 2017年7月12日
	 * @param goodsids 逗号分隔
	 * @return
	 */
	public static List<GoodsModel> getGoods(String goodsids, String phone){
		String sql="SELECT id, thumb, g_name, real_price, seller_id FROM goods WHERE id in("+ goodsids +")";
		List<GoodsModel> goods = GoodsModel.dao.find(sql);
		for (GoodsModel item : goods) {
			item.set("thumb", UserAppInfoController.ImgPath + item.getStr("thumb"));
			item.put("num", ShoppingCartModel.shoppingcart_goodsnum(phone, item.getInt("id")));
		}
		return goods;
	}
	
	/**
	 * 通过商品ID获取商品对象
	 * @author lifuqiang
	 * 2017-3-24
	 * @param goodsid
	 * @return
	 */
	public static GoodsModel getGoodsById(int goodsid){
		String sql="SELECT * FROM goods WHERE id = "+goodsid+"";
		GoodsModel goods = GoodsModel.dao.findFirst(sql);
		return goods;
	}
	
	/**
	 * 根据商品id 得到商品库存
	 * @author liushaomin
	 * 2017年7月13日
	 * @param goodsid
	 * @return
	 */
	public static GoodsModel getGoodsStockById(int goodsid){
		String sql="SELECT stock, g_name FROM goods WHERE id = "+goodsid+"";
		GoodsModel goods = GoodsModel.dao.findFirst(sql);
		return goods;
	}
	
	/**
	 * 通过搜索关键字获取所有商品(APP)-分页
	 * @author lifuqiang
	 * 2016-12-12
	 * @param key:关键字
	 * @param pageNumber：页码
	 * @return
	 */
	public Page<GoodsModel> getGoodsByKey(String key, int pageNumber){
		String sql="SELECT g.id, g.thumb,g_name, g.ratio, g.seller_id, g.c_id, g.sales AS sales, real_price AS real_price, g.c_time as c_time ";
		String str = "from goods g, buyer b , buyer_statistics bs where g.sale = 0 and g.seller_id = bs.buyer_id and bs.isshop = 1 and g.audit = 1 and g.seller_id = b.user_id and b.status = 0 and g.g_name like '%"+key+"%' OR g.content LIKE '%"+key+"%' GROUP BY g.id ORDER BY g.sales DESC,g.real_price DESC, c_time DESC";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
	
	/**
	 * 通过商品类别父节点获取所属所有商品(APP)-分页
	 * @author lifuqiang
	 * 2016-12-12
	 * @param categroy_id：商品类别id
	 * @param pageNumber：页码
	 * @return
	 */
	public Page<GoodsModel> getAllGoodsByCate(int categroy_id, int pageNumber){
		String categroy_pid  = GoodsCategroyModel.getCategoryPID_By_ID(String.valueOf(categroy_id));
		String sql="SELECT g.id, g.thumb,g_name, g.real_price, g.ratio, g.seller_id, g.c_id, g.sales, g.c_time as c_time ";
		String str = "from goods g, goods_categroy gc, buyer b , buyer_statistics bs where g.seller_id = bs.buyer_id and bs.isshop = 1 and g.audit = 1 and g.sale = 0 and g.seller_id = b.user_id and b.status = 0 and g.c_id = gc.id AND gc.pid > "+categroy_pid+" *100 AND gc.pid < ("+categroy_pid+"+1) *100 ORDER BY c_time DESC";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
	
	/**
	 *  得到商品详情。管理端。
	 * @param phone
	 * @param gid
	 * @return
	 */
	public GoodsModel  getGoodsDetail(String phone,String gid){
		String sql="SELECT * FROM goods a ,buyer b ,buyer_statistics bs WHERE a.`seller_id`=b.`user_id` and b.user_id = bs.buyer_id AND a.id="+gid ;
		return dao.findFirst(sql);
	}
	
	/**
	 * 通过用户手机号获取该用户自己发布商品的列表
	 * @author lifuqiang
	 * 2016-12-12
	 * @param phone
	 * @param pageNumber
	 * @param sale
	 * @return
	 */
	public Page<GoodsModel> saleshipments(String phone, int pageNumber, int sale){
		String sql="select id, thumb, sale, ratio, g_name,audit, real_price,n_reason, c_time as c_time ";
		String str = "from goods, buyer where phone ='"+phone+"' and seller_id = user_id and sale="+sale+" ORDER BY audit ASC, c_time DESC";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
	
	/**
	 * 获取ta人的店铺商品列表接口（分页）
	 * @author lifuqiang
	 * 2017-1-9
	 * @param phone
	 * @param pageNumber
	 * @return
	 */
	public Page<GoodsModel> getHisShopGoods(String phone, int pageNumber){
		String sql="select id, thumb, stock, sales, ratio, g_name, seller_id, real_price, c_time as c_time ";
		String str = "from goods, buyer where phone ='"+phone+"' and audit = 1 and seller_id = user_id and sale=0 ORDER BY c_time DESC";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
	
	/**
	 * 获取已关注的人所发布的商品
	 * 分页
	 * @author lifuqiang
	 * 2016-12-13
	 * @param phone
	 * @param pageNumber
	 * @return
	 */
	public Page<GoodsModel> getGoodsByFavorite(String phone, int pageNumber){
		String sql="select g.id, g.sales, thumb, g_name, real_price, ratio, seller_id, c_time AS c_time ";
		String str = "from goods g, buyer b where g.audit = 1 and g.seller_id = b.user_id and b.status = 0 and g.sale=0 and g.seller_id in (select b.user_id from buyer b where b.phone in (select to_phone from favorite where from_phone='"+phone+"')) order by c_time desc";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
	
	/**
	 * 首页-通过兴趣id得到用户感兴趣的商品list（6个）
	 * @author lifuqiang
	 * 2016-12-16
	 * @param categroyid
	 * @return
	 */
	public List<GoodsModel> getGoodsByCategroyid(String categroyid){
		String pids = GoodsCategroyModel.getCategoryPID_By_ID(categroyid);
		String sql = "select distinct g.id, g.g_name, g.real_price, g.thumb, g.ratio, g.seller_id, g.sales, g.c_time FROM goods g, goods_categroy gc, buyer_statistics bs WHERE g.seller_id = bs.buyer_id and bs.isshop = 1 and g.sale = 0 AND g.audit = 1 AND FLOOR(gc.pid / 100) in ("+pids+") ORDER BY sales DESC, c_time DESC LIMIT 6";
		List<GoodsModel> goods_list_hobby = dao.find(sql);
		return goods_list_hobby;
	}
	
	/**
	 * 首页--热门的商品（销量最高的）（6个）
	 * @author lifuqiang
	 * 2016-12-16
	 * @return
	 */
	public List<GoodsModel> getHotGoods(){
		String sql = "SELECT g.id, g_name, real_price, thumb, g.seller_id, g.c_time, sales from goods g , buyer_statistics bs where g.seller_id = bs.buyer_id and bs.isshop = 1 and g.audit = 1 and g.sale = 0 ORDER BY g.sales DESC, c_time DESC LIMIT 6 ";
		List<GoodsModel> goods_list_Hot = dao.find(sql);
		return goods_list_Hot;
	}
	
	/**
	 * 搜索--获取热门的搜索商品10个
	 * @author lifuqiang
	 * 2016-12-16
	 * @return
	 */
	public List<GoodsModel> getHotGoodsBySearch(){
		String sql = "SELECT g.id, g_name, real_price, thumb,g.seller_id, c_time,SUM(goods_num) as num from goods g, order_goods og, buyer b where g.audit = 1 and g.seller_id = b.user_id and b.status = 0 and g.id = og.goods_id AND g.id in (select group_concat(goods_id) from order_goods group by goods_id) group by goods_id ORDER BY num DESC LIMIT 10 ";
		List<GoodsModel> goods_list_HotSearch = dao.find(sql);
		return goods_list_HotSearch;
	}
	
	/**
	 * state = 0；减少库存，增加销量（下订单）
	 * state = 1；增加库存，减少销量（取消订单）
	 * @author lifuqiang
	 * 2016-12-17
	 * @param goodsid
	 * @param state
	 * @param goodsnum
	 * @return
	 */
	public void edit_stock_sales(int goodsid, int state, int goodsnum){
		int stock = 0;
		int sales = 0;
		GoodsModel goods = GoodsModel.dao.findById(goodsid);
		if(goods != null){
			if(0 == state){
				if(goods.getInt("stock") >= goodsnum){
					stock = goods.getInt("stock") - goodsnum;
					sales = goods.getInt("sales") + goodsnum;
					goods.set("stock", stock).set("sales", sales).update();
				}
			}else{
				stock = goods.getInt("stock") + goodsnum;
				sales = goods.getInt("sales") - goodsnum;
				goods.set("stock", stock).set("sales", sales).update();
			}
		}
	}

	/**
	 * 获取商家未审核过的商品数量
	 * 审核状态包括 未审核通过/审核未通过的商品
	 * 并且商品是上架状态
	 * @author liushaomin
	 * 2017年2月28日
	 * @param userid
	 * @return
	 */
	public static Long getUnaudited_Num(int userid) {
		return Db.queryLong("select count(1) from goods where seller_id = '" + userid + "' and audit IN (0,2) and sale = 0");
	}

	/**
	 * 获取商家已下架的商品数量
	 * 审核状态 是审核通过的
	 * @author liushaomin
	 * 2017年2月28日
	 * @param userid
	 * @return
	 */
	public static Long getInvalid_Num(int userid) {
		return Db.queryLong("select count(1) from goods where seller_id = '" + userid + "' and audit =1 and sale = 1");
	}

	/**
	 * 得到商家  不同类别（一级类别）的商品的总数量
	 * @author liushaomin
	 * 2017年2月28日
	 * @param useruid
	 * @param pid
	 * @return
	 */
	public static Long getCategroyGoods_Num(int useruid, int pid) {
		String pids = GoodsCategroyModel.getTwoLevelid(pid);
		return Db.queryLong("select count(1) from goods where c_id in (" + pids + ") and seller_id = "+ useruid +" and audit =1 and sale = 0");
	}

	/**
	 * 获取一级pid下的所有商品
	 * @author liushaomin
	 * 2017年2月28日
	 * @param phone
	 * @param pid
	 * @param pageNumber
	 * @return
	 */
	public static Page<GoodsModel> getGoodsListByPid(String phone, int pid, int pageNumber) {
		int userid = UserAppModel.getUserid(phone);
		String pids = GoodsCategroyModel.getTwoLevelid(pid);
		String sql = "select * ";
		String str = "from goods where c_id in (" + pids + ") and seller_id = "+ userid +" and audit =1 and sale = 0";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
	
	/**
	 * 获取商家已下架或未审核的商品
	 * @author liushaomin
	 * 2017年3月2日
	 * @param phone
	 * @param type unaudited、invalid
	 * @param pageNumber
	 * @return
	 */
	public static Page<GoodsModel> getOther_Goods(String phone, String type, int pageNumber) {
		int userid = UserAppModel.getUserid(phone);
		String sql = "select * ";
		String str = "";
		if(type.equals("unaudited")){
			str = "from goods where seller_id = '" + userid + "' and audit IN (0,2) and sale = 0";
		}else if(type.equals("invalid")){
			str = "from goods where seller_id = '" + userid + "' and audit =1 and sale = 1";
		}
		Page<GoodsModel> goods_list = dao.paginate(pageNumber, Utils.pageSize, sql, str);
		return goods_list;
	}
	
	/**
	 * 商品上传量
	 * @author lifuqiang
	 * 2017-4-20
	 * @return
	 */
	public static long goodsNumber(){
		String sql = "select * from goods";
		List<GoodsModel> list = dao.find(sql);
		return list.size();
	}

	/**
	 * 首页-发现数据
	 * @author liushaomin
	 * 2017年7月6日
	 * @return
	 */
	public static Page<GoodsModel> getGoodsByFaXian(int pageNumber) {
		String sql = "select * ";
		String str = "from goods where audit = 1 and sale = 0 ORDER BY c_time DESC";
		Page<GoodsModel> goods_list = dao.paginate(pageNumber, Utils.pageSize, sql, str);
		return goods_list;
	}
	
	/**
	 * 得到商品集合的总价格  未加邮费
	 * @author liushaomin
	 * 2017年7月12日
	 * @param goods list
	 * @return
	 */
	public static double getTotal(List<GoodsModel> item_goodslist) {
		double total = 0;
		for (GoodsModel item : item_goodslist) {
			total += Utils.muti(item.getFloat("real_price"), item.getInt("num"));
		}
		return total;
	}
	
	/**
	 * 得到商品集合的总价格  未加邮费
	 * @author liushaomin
	 * 2017年7月12日
	 * @param goods 单个商品
	 * @return
	 */
	public static double getTotal(GoodsModel goods, int goodsnum) {
		return Utils.muti(goods.getFloat("real_price"), goodsnum);
	}

	/**
	 * 得到商品集合的总价格  加邮费
	 * @author liushaomin
	 * 2017年7月12日
	 * @param goods list
	 * @return
	 */
	public static Object getTotal_ContainPostage(List<GoodsModel> item_goodslist, String seller_id) {
		double total = 0;
		for (GoodsModel item : item_goodslist) {
			total = Utils.add(total, Utils.muti(item.getFloat("real_price"), item.getInt("num")));
		}
		return getTotal_ContainPostage(total, seller_id);
	}
	
	/**
	 * 得到单个商品的总价格  加邮费 
	 * @author liushaomin
	 * 2017年7月12日
	 * @param goods 单个商品
	 * @param seller_id
	 * @param goodsnum
	 * @return
	 */
	public static Object getTotal_ContainPostage(GoodsModel goods, String seller_id, int goodsnum) {
		double total = Utils.muti(goods.getFloat("real_price"), goodsnum);
		return getTotal_ContainPostage(total, seller_id);
	}

	/**
	 * 计算订单的实际价格（商品的价格 + 运费）
	 * @author liushaomin
	 * 2017年7月14日
	 * @param goods_money 所有商品的总价格
	 * @param seller_id 商家id
	 * @return
	 */
	public static String getTotal_ContainPostage(double goods_money, String seller_id) {
		String sql = "select postage_type, mangou_money, postage_money from buyer_statistics where buyer_id = "+ seller_id +"";
		BuyerStatisticsModel buyer = BuyerStatisticsModel.dao.findFirst(sql);
		 if(buyer.getInt("postage_type") == 1){
			 if(goods_money < buyer.getDouble("mangou_money")){
				 goods_money = Utils.add(goods_money, buyer.getDouble("postage_money"));
				}
		 }else if(buyer.getInt("postage_type") == 2){
			 goods_money = Utils.add(goods_money, buyer.getDouble("postage_money"));
		 }
		return Utils.decimalFormat((float)goods_money);
	}
	
	/**
	 * 得到首页自营某类别的商品
	 * @author liushaomin
	 * 2017年8月25日
	 * @param goods_categroy_id 类别的id
	 * @param pageNumber
	 * @return
	 */
	public static Page<GoodsModel> getIndexZiYing(String goods_categroy_id, int pageNumber){
		String sql="select g.id, g.sales, thumb, g_name, real_price, ratio, seller_id, c_time AS c_time ";
		String str = "from goods g where g.audit = 1 and g.seller_id = 61 and g.sale = 0 and g.c_id in ( " + goods_categroy_id + " )";
		Page<GoodsModel> goods_list=dao.paginate(pageNumber, Utils.pageSize, sql,str);
		return goods_list;
	}
}
